package com.junsi.util;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import java.lang.reflect.Method;
import com.junsi.bean.ApproveRole;
import com.junsi.bean.Client;
import com.sun.java_cup.internal.runtime.virtual_parse_stack;

import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;
public class DownlaodExcel {

	
	
  public static void ClientExcel(HttpServletResponse resp,List<?> dataList,String sheetName){
	  
	  
	  
	  
	   String  userId="userDate";
	  String xlsxFileName = userId + new SimpleDateFormat("_yyyy/MM/dd_HH:mm:ss").format(new Date());
	  resp.setCharacterEncoding("utf-8");
		resp.setContentType("application/vnd.ms-excel");
		resp.setHeader("content-disposition", "attachment;filename="+ xlsxFileName + ".xlsx");
		OutputStream out=null;
		try {
			out = resp.getOutputStream();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String headers[] = { "Client_id", "Client_name", "Client_record", "Client_phone","Staff_name",
				"Client_remarks"};
		List<String> list1 = new ArrayList<String>();
		List<String> list2 = new ArrayList<String>();
		List<String> list3 = new ArrayList<String>();
		List<String> list4 = new ArrayList<String>();

		List<String> list6 = new ArrayList<String>();
		List<String> list7 = new ArrayList<String>();
		list1.add("编号");
		list2.add("名称");
		list3.add("来源渠道");
		list4.add("电话"); 
		list6.add("所有人"); 
		list7.add("追踪情况");
		Map<String, Object> headerMap = new LinkedHashMap<String, Object>();
		headerMap.put("编号", list1);
		headerMap.put("名称", list1);
		headerMap.put("来源渠道D", list1);
		headerMap.put("电话", list1);
		headerMap.put("所有人", list1);
		headerMap.put("追踪情况", list1);
		//标签页标题
		SXSSFWorkbook workBook = new SXSSFWorkbook();  
      Sheet sheet = workBook.createSheet(sheetName);//创建一个工作薄对象  
      sheet.setDefaultColumnWidth(15);//设置表格默认宽度
      
      /*************************表头样式*********************************/
      CellStyle style = workBook.createCellStyle();//创建样式对象  
      style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
      style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);//背景颜色设置
      style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
	    style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
	    style.setBorderRight(XSSFCellStyle.BORDER_THIN);
	    style.setBorderTop(XSSFCellStyle.BORDER_THIN);//顶部边框粗线
	    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中 
      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中  
	    style.setWrapText( true );
      // 设置字体  
      Font font = workBook.createFont();// 创建字体对象  
      font.setFontHeightInPoints((short) 12);// 设置字体大小  
      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置粗体  
      style.setFont(font);// 将字体加入到样式对象  
      
      /*************************数据样式*********************************/
      CellStyle dataStyle = workBook.createCellStyle();// 创建样式对象  
      dataStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
      dataStyle.setFillForegroundColor(HSSFColor.WHITE.index);//背景颜色设置
      dataStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
      dataStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
      dataStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
      dataStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
      dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
      dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
      //设置字体  
      Font dataFont = workBook.createFont();// 创建字体对象  
      dataFont.setFontHeightInPoints((short) 12);// 设置字体大小  
      dataFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 设置粗体  
      dataStyle.setFont(dataFont);// 将字体加入到样式对象  
	        
      /******************************写标题**************************************/
  	int r = 0;//行号
  	int c = 0;//列号
  	Row row = sheet.createRow(r++);
		Row row1 = sheet.createRow(r++);
  	for(Iterator<Map.Entry<String, Object>> entry = headerMap.entrySet().iterator(); entry.hasNext();) {
  		
  		Entry<String, Object> e = entry.next();
  		Object o = e.getValue();
  		if(o instanceof List) {
  			List<String> list = (List<String>)o;
  			if (list.size() == 1) {
					row.setHeightInPoints(23);// 设置行高23像素  
					Cell cell = row.createCell(c);
					cell.setCellValue(e.getKey());
					cell.setCellStyle(style);
					sheet.addMergedRegion(new CellRangeAddress(0, 1, c, c));
					// 第二行
					Cell cell1 = row1.createCell(c);
					cell1.setCellStyle(style);
					cell1.setCellValue(new HSSFRichTextString(e.getKey()));
					c++;
				} else {
					for (int i = 0; i < list.size(); i++) {
						Cell cell1 = row1.createCell(c);
						cell1.setCellStyle(style);
						cell1.setCellValue(new HSSFRichTextString(list.get(i)));
						Cell cell = row.createCell(c);
						cell.setCellStyle(style);
						cell.setCellValue(e.getKey());
						c++;
					}
					sheet.addMergedRegion(new CellRangeAddress(0, 0, c-list.size(), c-1));
				}
  		}
  	}
  	Row dataRow = null;
	for (Object bap : dataList) {
		
		dataRow = sheet.createRow(r++);
		for (int j = 0; j < headers.length; j++) {
			
			if (StringUtils.isNotBlank(headers[j])) {
				Cell cell = dataRow.createCell(j);
				cell.setCellStyle(dataStyle);
				String getMethodName = "get" + headers[j];
				try {
					Class tCls = bap.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(bap, new Object[] {});
					if (value != null) {
						// 判断值的类型后进行强制类型转换
						String textValue = null;
						if (value instanceof Date) {
							Date d = (Date) value;
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
							textValue = sdf.format(d);
						} else {
							// 其他数据类型都当作字符串简单处理
							textValue = value.toString();
						}
						if (textValue != null) {
							Pattern p = Pattern.compile("^[-+]?(\\d+(\\.\\d*)?|\\.\\d+)([eE][-+]?\\d+)?$"); 
							Matcher matcher = p.matcher(textValue);
							if (matcher.matches()) { // 是数字当作double处理
								double d = Double.parseDouble(textValue);
								BigDecimal b = new BigDecimal(d);
								double df = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
								cell.setCellValue(df);
							} else {
								cell.setCellValue(new HSSFRichTextString(textValue));
							}
						} else {
							cell.setCellValue("");
						}
					}
				} catch (Exception e) {
					
					e.printStackTrace();
				}
			}
		}
	}
	try {
		workBook.write(out);
	} catch (IOException e) {
		e.printStackTrace();
	}
	  
  }
  
  public static void excel(HttpServletResponse resp,List<?> dataList,String sheetName,Map<String, Object> headerMap,String[] headers){
	  
	  
	  
	  
	   String  userId="userDate";
	  String xlsxFileName = userId + new SimpleDateFormat("_yyyy/MM/dd_HH:mm:ss").format(new Date());
	  resp.setCharacterEncoding("utf-8");
		resp.setContentType("application/vnd.ms-excel");
		resp.setHeader("content-disposition", "attachment;filename="+ xlsxFileName + ".xlsx");
		OutputStream out=null;
		try {
			out = resp.getOutputStream();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		
		//标签页标题
		SXSSFWorkbook workBook = new SXSSFWorkbook();  
     Sheet sheet = workBook.createSheet(sheetName);//创建一个工作薄对象  
     sheet.setDefaultColumnWidth(15);//设置表格默认宽度
     
     /*************************表头样式*********************************/
     CellStyle style = workBook.createCellStyle();//创建样式对象  
     style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
     style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);//背景颜色设置
     style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
	    style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
	    style.setBorderRight(XSSFCellStyle.BORDER_THIN);
	    style.setBorderTop(XSSFCellStyle.BORDER_THIN);//顶部边框粗线
	    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中 
     style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中  
	    style.setWrapText( true );
     // 设置字体  
     Font font = workBook.createFont();// 创建字体对象  
     font.setFontHeightInPoints((short) 12);// 设置字体大小  
     font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置粗体  
     style.setFont(font);// 将字体加入到样式对象  
     
     /*************************数据样式*********************************/
     CellStyle dataStyle = workBook.createCellStyle();// 创建样式对象  
     dataStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
     dataStyle.setFillForegroundColor(HSSFColor.WHITE.index);//背景颜色设置
     dataStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
     dataStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
     dataStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
     dataStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
     dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
     dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
     //设置字体  
     Font dataFont = workBook.createFont();// 创建字体对象  
     dataFont.setFontHeightInPoints((short) 12);// 设置字体大小  
     dataFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 设置粗体  
     dataStyle.setFont(dataFont);// 将字体加入到样式对象  
	        
     /******************************写标题**************************************/
 	int r = 0;//行号
 	int c = 0;//列号
 	Row row = sheet.createRow(r++);
		Row row1 = sheet.createRow(r++);
 	for(Iterator<Map.Entry<String, Object>> entry = headerMap.entrySet().iterator(); entry.hasNext();) {
 		
 		Entry<String, Object> e = entry.next();
 		Object o = e.getValue();
 		if(o instanceof List) {
 			List<String> list = (List<String>)o;
 			if (list.size() == 1) {
					row.setHeightInPoints(23);// 设置行高23像素  
					Cell cell = row.createCell(c);
					cell.setCellValue(e.getKey());
					cell.setCellStyle(style);
					sheet.addMergedRegion(new CellRangeAddress(0, 1, c, c));
					// 第二行
					Cell cell1 = row1.createCell(c);
					cell1.setCellStyle(style);
					cell1.setCellValue(new HSSFRichTextString(e.getKey()));
					c++;
				} else {
					for (int i = 0; i < list.size(); i++) {
						Cell cell1 = row1.createCell(c);
						cell1.setCellStyle(style);
						cell1.setCellValue(new HSSFRichTextString(list.get(i)));
						Cell cell = row.createCell(c);
						cell.setCellStyle(style);
						cell.setCellValue(e.getKey());
						c++;
					}
					sheet.addMergedRegion(new CellRangeAddress(0, 0, c-list.size(), c-1));
				}
 		}
 	}
 	Row dataRow = null;
	for (Object bap : dataList) {
		
		dataRow = sheet.createRow(r++);
		for (int j = 0; j < headers.length; j++) {
			
			if (StringUtils.isNotBlank(headers[j])) {
				Cell cell = dataRow.createCell(j);
				cell.setCellStyle(dataStyle);
				String getMethodName = "get" + headers[j];
				try {
					Class tCls = bap.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(bap, new Object[] {});
					if (value != null) {
						// 判断值的类型后进行强制类型转换
						String textValue = null;
						if (value instanceof Date) {
							Date d = (Date) value;
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
							textValue = sdf.format(d);
						} else {
							// 其他数据类型都当作字符串简单处理
							textValue = value.toString();
						}
						if (textValue != null) {
							Pattern p = Pattern.compile("^[-+]?(\\d+(\\.\\d*)?|\\.\\d+)([eE][-+]?\\d+)?$"); 
							Matcher matcher = p.matcher(textValue);
							if (matcher.matches()) { // 是数字当作double处理
								double d = Double.parseDouble(textValue);
								BigDecimal b = new BigDecimal(d);
								double df = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
								cell.setCellValue(df);
							} else {
								cell.setCellValue(new HSSFRichTextString(textValue));
							}
						} else {
							cell.setCellValue("");
						}
					}
				} catch (Exception e) {
					
					e.printStackTrace();
				}
			}
		}
	}
	try {
		workBook.write(out);
	} catch (IOException e) {
		e.printStackTrace();
	}
	  
 }
}
